﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;

using ECS.DAL;
using ECS.DAL.Entity;
using ECS.Framework;

namespace ECS.DAL.Component
{
    public class EicsSetupComponent : BaseSQLModel
    {
        #region private properties
        private const string SQL_QUERY = "SELECT [setup_id] " +
                                        " ,[station_id] ,[type] ,[setting_name] ,[setting_value_string] ,[setting_value_number] " +
                                        " ,[setting_value_datetime] ,[create_date] ,[create_by] " +
                                        " FROM [EICS].[dbo].[t_eics_setup]" +
                                        " WHERE 1=1 ";

        private const string SQL_PROC_QUERY = "usp_GetSetupRecord"; // @AppId, Null, Null, Null, null, null,null, @Device, @UserId";

        private const string SQL_PARAM_SEARCH_SETUP_ID = " AND [setup_id] = @setup_id ";

        private const string SQL_INSERT = "INSERT INTO [dbo].[t_eics_setup] " +
           "([setup_id] ,[station_id],[type],[setting_name],[setting_value_string] ,[setting_value_number] " +
           " ,[setting_value_datetime] ,[create_date],[create_by]) " +
            "VALUES " +
           " (@setup_id ,@station_id ,@type ,@setting_name ,@setting_value_string " +
           " ,@setting_value_number ,@setting_value_datetime ,@create_date,@create_by) ";

        private const string SQL_UPDATE = "UPDATE [dbo].[t_eics_setup] " +
                                        "SET [station_id] = @station_id " +
                                      " ,[type] = @type " +
                                      " ,[setting_name] = @setting_name " +
                                      " ,[setting_value_string] = @setting_value_string " +
                                      " ,[setting_value_number] = @setting_value_number " +
                                      " ,[setting_value_datetime] = @setting_value_datetime " +
                                      " ,[create_date] = @create_date " +
                                      " ,[create_by] = @create_by " +
                                      " WHERE [setup_id] = @setup_id";

        private const string SQL_PROC_UPDATE = "exec usp_UpdateSetupRecord " +
            "@AppId,  @setup_id, @type, @station_id, @setting_name, @setting_value_string, @setting_value_number, @setting_value_datetime, " +
            "@Device, @UserId ";

        private const string SQL_DELETE = "DELETE FROM [dbo].[t_eics_setup] WHERE [setup_id] = @setup_id";
        #endregion

        #region public properties
        
        #endregion

        #region constructor
        public EicsSetupComponent()
        { 
        
        }
        #endregion

        #region private methods
        
        #endregion

        #region public mehods
        /// <summary>
        /// get Eics setup data to list
        /// </summary>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public List<ObjectContext> Search(ObjectContext parameter)
        {
            DataSet ds = new DataSet();
            string status = string.Empty;
            string errorCode = string.Empty;
            string message = string.Empty;
            EicsSetup param = (EicsSetup)parameter;
            List<ObjectContext> items = new List<Entity.ObjectContext>();
            using (Entities entities = new Entities())
            {
                StringBuilder sqlString = new StringBuilder();
                sqlString.Append(SQL_PROC_QUERY);
                //sqlString.Append(SQL_QUERY);

                //if (!string.IsNullOrEmpty(param.SetupId))
                //{
                //    sqlString.Append(SQL_PARAM_SEARCH_SETUP_ID);
                //    sqlString.Replace("@setup_id", BaseSQLModel.SqlWithQoute(param.SetupId));
                //}
                
                entities.Open();
                Console.WriteLine(sqlString.ToString());
                DbCommand cmd = entities.CreateCommand(sqlString.ToString());
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", param.AppId));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", param.Device));
                cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", param.UserId));
                
                //START remark this code block for new sproc
                DbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    EicsSetup data = new EicsSetup();
                    data.SetupId = base.GetString(0, reader);
                    data.StationId = base.GetString(1, reader);
                    data.SetupType = base.GetString(2, reader);
                    data.SettingName = base.GetString(3, reader);
                    data.ValueString = base.GetString(4, reader);
                    data.ValueNumber = (float?)base.GetDouble(5, reader);
                    data.ValueDate = (DateTime?)base.GetDate(6, reader);
                    data.CreateDate = (DateTime)base.GetDate(7, reader);
                    data.CreateBy = base.GetString(8, reader);
                    data.NotChangeObject();
                    items.Add(data);
                }
                //END remark this code block for new sproc

                //START un-mark this code for new sproc that return more 3 fields
                //DbDataAdapter adapter = entities.CreateDataAdapter(cmd);
                //adapter.Fill(ds);
                //if (ds != null && ds.Tables.Count > 0)
                //{
                //    status = ds.Tables[0].Rows[0]["status"].ToString();
                //    errorCode = ds.Tables[0].Rows[0]["ErrorCode"].ToString();
                //    message = ds.Tables[0].Rows[0]["Message"].ToString();
                //    if (status.Equals("PASS"))
                //    {
                //        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                //        {
                //            EicsSetup data = new EicsSetup();
                //            data.SetupId = ds.Tables[0].Rows[i]["setup_id"].ToString();
                //            data.StationId = ds.Tables[0].Rows[i]["station_id"].ToString();
                //            data.SetupType = ds.Tables[0].Rows[i]["type"].ToString();
                //            data.SettingName = ds.Tables[0].Rows[i]["setting_name"].ToString();
                //            if (ds.Tables[0].Rows[i]["setting_value_string"] != DBNull.Value)
                //            {
                //                data.ValueString = ds.Tables[0].Rows[i]["setting_value_string"].ToString();
                //            }
                //            if (ds.Tables[0].Rows[i]["setting_value_number"] != DBNull.Value)
                //            {
                //                data.ValueNumber = Convert.ToSingle(ds.Tables[0].Rows[i]["setting_value_number"]);
                //            }
                //            if (ds.Tables[0].Rows[i]["setting_value_datetime"] != DBNull.Value)
                //            {
                //                data.ValueDate = Convert.ToDateTime(ds.Tables[0].Rows[i]["setting_value_datetime"]);
                //            }
                //            data.CreateDate = Convert.ToDateTime(ds.Tables[0].Rows[i]["create_date"].ToString());
                //            data.CreateBy = ds.Tables[0].Rows[i]["create_by"].ToString();
                //            data.NotChangeObject();
                //            items.Add(data);
                //        }
                //    }
                //    else
                //    {
                //        CommonMessageBox.ShowMessageWarning(errorCode + "\r\n" + message);
                //    }
                //}
                //END un-mark this code for new sproc that return more 3 fields

            }
            return items;
        }

        public override string GetInsertSQL(ObjectContext param)
        {
            /* " (@setup_id ,@station_id ,@type ,@setting_name ,@setting_value_string " +
          " ,@setting_value_number ,@setting_value_datetime ,@create_date,@create_by) ";*/
            EicsSetup parameter = (EicsSetup)param;
            StringBuilder sqlString = new StringBuilder();
            //insert command
            sqlString.Append(SQL_INSERT);
            sqlString.Replace("@setup_id", BaseSQLModel.SqlWithQoute(parameter.SetupId));
            sqlString.Replace("@station_id", BaseSQLModel.SqlWithQoute(parameter.StationId));
            sqlString.Replace("@type", BaseSQLModel.SqlWithQoute(parameter.SetupType));
            sqlString.Replace("@setting_name", BaseSQLModel.SqlWithQoute(parameter.SettingName));
            sqlString.Replace("@setting_value_string", BaseSQLModel.SqlWithQoute(parameter.ValueString));
            sqlString.Replace("@setting_value_number", parameter.ValueNumber == null? "NULL": parameter.ValueNumber.ToString());
            sqlString.Replace("@setting_value_datetime", parameter.ValueDate == null ? "NULL" : BaseSQLModel.SqlWithQoute(string.Format("{0:yyyy-MM-dd}", parameter.ValueDate)));
            sqlString.Replace("@create_date", BaseSQLModel.SqlWithQoute(parameter.CreateDate.ToString("yyyy-MM-dd")));
            sqlString.Replace("@create_by", BaseSQLModel.SqlWithQoute(parameter.CreateBy));
            return sqlString.ToString();
        }

        public override string GetUpdateSQL(ObjectContext param)
        {
            //store procedure
            //@in_vchApplicationID			VARCHAR(40), -- Required
            //@in_vchSetupID					VARCHAR(40) = NULL,  --Required
            //@in_vchType						VARCHAR(10),  -- Required
            //@in_vchStationID				VARCHAR(40),  -- Required
            //@in_vchSettingName				VARCHAR(50),  -- Required
            //@in_vchSettingValueString		VARCHAR(50), 
            //@in_fltSettingValueNumber		FLOAT, 
            //@in_dtSettingValueDateTime		DATETIME, 
            //@in_vchDevice				    NVARCHAR(50),  -- Required
            //@in_vchUserID				    NVARCHAR(20)  -- Required
            
            // sql command
            //"@AppId,  @setup_id, @type, @station_id, @setting_name, @setting_value_string, @setting_value_number, @setting_value_datetime, " +
            //"@Device, @UserId ";

            EicsSetup parameter = (EicsSetup)param;
            StringBuilder sqlString = new StringBuilder();
            //sqlString.Append(SQL_UPDATE);
            sqlString.Append(SQL_PROC_UPDATE);
            sqlString.Replace("@AppId", BaseSQLModel.SqlWithQoute(parameter.AppId));
            sqlString.Replace("@setup_id", BaseSQLModel.SqlWithQoute(parameter.SetupId));
            sqlString.Replace("@station_id", BaseSQLModel.SqlWithQoute(parameter.StationId));
            sqlString.Replace("@type", BaseSQLModel.SqlWithQoute(parameter.SetupType));
            sqlString.Replace("@setting_name", BaseSQLModel.SqlWithQoute(parameter.SettingName));
            sqlString.Replace("@setting_value_string", BaseSQLModel.SqlWithQoute(parameter.ValueString));
            sqlString.Replace("@setting_value_number", parameter.ValueNumber == null ? "NULL" : parameter.ValueNumber.ToString());
            sqlString.Replace("@setting_value_datetime", parameter.ValueDate == null ? "NULL" : BaseSQLModel.SqlWithQoute(string.Format("{0:yyyy-MM-dd}", parameter.ValueDate)));
            //sqlString.Replace("@create_date", BaseSQLModel.SqlWithQoute(parameter.CreateDate.ToString("yyyy-MM-dd")));
            //sqlString.Replace("@create_by", BaseSQLModel.SqlWithQoute(parameter.CreateBy));
            sqlString.Replace("@Device", BaseSQLModel.SqlWithQoute(parameter.Device));
            sqlString.Replace("@UserId", BaseSQLModel.SqlWithQoute(parameter.UserId));

            return sqlString.ToString();
        }

        public override string GetDeleteSQL(ObjectContext param)
        {
            EicsSetup parameter = (EicsSetup)param;
            StringBuilder sqlString = new StringBuilder();
            //insert command
            sqlString.Append(SQL_DELETE);
            sqlString.Replace("@setup_id", BaseSQLModel.SqlWithQoute(parameter.SetupId));
            return sqlString.ToString();
        }
        /// <summary>
        /// Insert to data source
        /// </summary>
        /// <param name="param"></param>
        public override void Insert(ObjectContext param)
        {
          
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetInsertSQL(param));
                    entities.SetCommand(cmd);
                    int m_iRecordChanged = entities.SaveChanges();
                    if (m_iRecordChanged < 1)
                    {
                        throw new Exception("t_eics_setup insert unsuccess.");
                    }
                }
                transaction.Complete();
            }
        }
        /// <summary>
        /// update to datasource
        /// </summary>
        /// <param name="param"></param>
        public override void Update(ObjectContext param)
        {
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetUpdateSQL(param));
                    entities.SetCommand(cmd);
                    int m_iRecordChanged = entities.SaveChanges();
                    if (m_iRecordChanged < 1)
                    {
                        throw new Exception("t_eics_setup update unsuccess.");
                    }
                }
                transaction.Complete();
            }
        }
        /// <summary>
        /// delete from datasource
        /// </summary>
        public override void Delete(ObjectContext param)
        {
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetDeleteSQL(param));
                    entities.SetCommand(cmd);
                    int m_iRecordChanged = entities.SaveChanges();
                    if (m_iRecordChanged < 0)
                    {
                        throw new Exception("t_eics_setup delete unsuccess.");
                    }
                }
                transaction.Complete();
            }
        }
        /// <summary>
        /// special case for store procedure to update data that return result as record
        /// </summary>
        /// <param name="items"></param>
        public override void SaveChanges(List<ObjectContext> items)
        {
            string sqlCmd = string.Empty;
            string textMessage = string.Empty;
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DataSet ds = new DataSet();
                    DbCommand cmd = entities.CreateCommand(sqlCmd);
                    
                    DbDataAdapter adapter = entities.CreateDataAdapter(sqlCmd);
                    adapter.SelectCommand = cmd;
                    //cmd.CommandType = CommandType.StoredProcedure;
                    foreach (ObjectContext item in items)
                    {
                        switch (item.GetObjectSate())
                        {
                            case OBJECT_STATE.Insert:
                                sqlCmd = GetInsertSQL(item);
                                textMessage = "Insert ";
                                break;
                            case OBJECT_STATE.Update:
                                sqlCmd = GetUpdateSQL(item);
                                textMessage = "Update ";
                                break;
                            case OBJECT_STATE.Delete:
                                sqlCmd = GetDeleteSQL(item);
                                textMessage = "Delete ";
                                break;
                            default:
                                sqlCmd = string.Empty;
                                break;
                        }
                        if (!string.IsNullOrEmpty(sqlCmd))
                        {
                            //DbCommand cmd = entities.CreateCommand(sqlCmd);
                            //cmd.CommandType = CommandType.StoredProcedure;
                            //entities.SetCommand(cmd);
                            //int m_iRecordChanged = entities.SaveChanges();
                            //int m_iRecordChanged = cmd.ExecuteNonQuery();
                            //if (m_iRecordChanged < 0)
                            //{
                            //    throw new Exception(string.Format("{0} transaction failed", textMessage));
                            //}
                            cmd.CommandText = sqlCmd;
                            adapter.Fill(ds);
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                if (!ds.Tables[0].Rows[0]["Status"].ToString().Equals("PASS"))
                                {
                                    string errorMessage = string.Format("Status: {0} Error Code:{1}\r\nError:{2}", ds.Tables[0].Rows[0]["Status"].ToString(), ds.Tables[0].Rows[0]["ErrorCode"].ToString(), ds.Tables[0].Rows[0]["Message"].ToString());
                                    throw new Exception(errorMessage);
                                }
                            }
                            item.NotChangeObject();
                        }
                    }
                }
                transaction.Complete();
            }
        }
        #endregion
    }
}
